package cn.rulian.car.outbill.service;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.Tuple;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import cn.rulian.base.basis.bo.CommonProperty;
import cn.rulian.base.basis.dao.CommonPropertyDao;
import cn.rulian.base.common.service.BaseService;
import cn.rulian.base.common.util.Num;
import cn.rulian.base.common.util.xx;
import cn.rulian.base.common.vo.Result;
import cn.rulian.car.outbill.bo.OutBill;
import cn.rulian.car.outbill.dao.OutBillDao;
import cn.rulian.car.outbill.dto.Rate;
import cn.rulian.car.outbill.vo.OutBillFooter;
import cn.rulian.car.outbill.vo.OutBillVo;

/**
 * 外出单
 */
@Service
@Transactional(readOnly = true)
public class OutBillService extends BaseService<OutBill>
{

	// private final Logger log = LoggerFactory.getLogger(getClass());

	@Autowired
	private OutBillDao dao;

	@Autowired
	private CommonPropertyDao commonPropertyDao;

	@Autowired
	EntityManager em;

	@Override
	protected OutBillDao getDao()
	{
		return dao;
	}

	/**
	 * 查询
	 */
	public Result query(final OutBillVo vo)
	{
		Sort sort = new Sort("desc".equals(vo.getOrder())?Direction.DESC:Direction.ASC, vo.getSort()); // 排序
		Pageable pageable = new PageRequest(vo.getPage() - 1, vo.getRows(), sort); // 分页查询条件

		//动态查询对象
		Specification<OutBill> specification = new Specification<OutBill>()
		{
			@Override
			public Predicate toPredicate(Root<OutBill> root, CriteriaQuery<?> cq, CriteriaBuilder cb)
			{
				return buildPredicate(root, cb, vo);
			}
		};

		// 查询
		Page<OutBill> page = dao.findAll(specification, pageable);
		Result result = Result.getResult(page);

		//构建合计项
		List<OutBillFooter> footer = new ArrayList<>();
		//(1)标题
		OutBillFooter title = new OutBillFooter();
		title.setUseNum("使用里程");
		title.setUseTime("总时长");
		title.setWaitTime("等待时长");
		title.setFuelNum("加油金额");
		title.setOtherFee("过 / 停费");
		title.setAllFee("总费用");
		footer.add(title);

		//(2)本页合计
		List<OutBill> ls = page.getContent();
		footer.add(this.getOnePageSum(ls));
		
		//(3)所有符合条件的合计项
		footer.add(this.getAllSum(vo));
		
		result.setFooter(footer);
		return result;
	}

	/**
	 * 汇总一页数据的合计信息
	 * @param ls
	 * @return
	 */
	private OutBillFooter getOnePageSum(List<OutBill> ls)
	{
		OutBillFooter onePageSum = new OutBillFooter();
		
		int useNum = 0; // 使用里程
		double useTime = 0; // 使用时长(小时，保留一位小数)
		double waitTime = 0; // 等待时间(小时，保留一位小数)
		double fuelNum = 0; // 加油费
		double otherFee = 0; // 其他费用（过路费、停车费）
		double allFee = 0; // 全部费用
		
		for(OutBill bo:ls)
		{
			if(bo.getUseNum()!=null)
			{
				useNum += bo.getUseNum();
			}
			if(bo.getUseTime()!=null)
			{
				useTime = Num.add(useTime, bo.getUseTime());
			}
			if(bo.getWaitTime()!=null)
			{
				waitTime = Num.add(waitTime, bo.getWaitTime());
			}
			if(bo.getFuelNum()!=null)
			{
				fuelNum = Num.add(fuelNum, bo.getFuelNum());
			}
			if(bo.getOtherFee()!=null)
			{
				otherFee = Num.add(otherFee, bo.getOtherFee());
			}
			if(bo.getAllFee()!=null)
			{
				allFee = Num.add(allFee, bo.getAllFee());
			}
		}
		
		onePageSum.setUseNum(useNum + "");
		onePageSum.setUseTime(Num.format(useTime,"0.0"));
		onePageSum.setWaitTime(Num.format(waitTime,"0.0"));
		onePageSum.setFuelNum(Num.format(fuelNum,"0.0"));
		onePageSum.setOtherFee(Num.format(otherFee,"0.0"));
		onePageSum.setAllFee(Num.format(allFee,"0.0"));
		onePageSum.setEndTime("本页合计");
		
		return onePageSum;
	}

	/**
	 * 获取所有符合条件的数据合计
	 */
	private OutBillFooter getAllSum(OutBillVo vo)
	{

		CriteriaBuilder cb = em.getCriteriaBuilder();
		CriteriaQuery<Tuple> cq = cb.createTupleQuery();

		// from
		Root<OutBill> root = cq.from(OutBill.class);

		// where
		cq.where(this.buildPredicate(root, cb, vo));

		return getSumData(cb, cq, root);
	}

	/**
	 * 查询合计数据
	 */
	private OutBillFooter getSumData(CriteriaBuilder cb, CriteriaQuery<Tuple> cq, Root<OutBill> root)
	{
		// 合计项
		cq.multiselect(
				cb.sum(root.<Integer> get("useNum")).alias("useNum"), 
				cb.sum(root.<Double> get("useTime")).alias("useTime"),
				cb.sum(root.<Double> get("waitTime")).alias("waitTime"), 
				cb.sum(root.<Double> get("fuelNum")).alias("fuelNum"),
				cb.sum(root.<Double> get("otherFee")).alias("otherFee"), 
				cb.sum(root.<Double> get("allFee")).alias("allFee"));
		
		// 查询
		List<Tuple> rows = em.createQuery(cq).getResultList();

		Tuple tuple = rows.get(0);
		Long useNum = tuple.get("useNum", Long.class); // 使用里程
		Double useTime = tuple.get("useTime", Double.class); // 使用时长(小时，保留一位小数)
		Double waitTime = tuple.get("waitTime", Double.class); // 等待时间(小时，保留一位小数)
		Double fuelNum = tuple.get("fuelNum", Double.class); // 加油费
		Double otherFee = tuple.get("otherFee", Double.class); // 其他费用（过路费、停车费）
		Double allFee = tuple.get("allFee", Double.class); // 全部费用

		OutBillFooter all = new OutBillFooter();
		all.setUseNum(useNum==null ? "0" : useNum.toString());
		all.setUseTime(useTime==null ? "0" : Num.format(useTime,"0.0"));
		all.setWaitTime(waitTime==null ? "0" : Num.format(waitTime,"0.0"));
		all.setFuelNum(fuelNum==null ? "0" : Num.format(fuelNum,"0.0"));
		all.setOtherFee(otherFee==null ? "0" : Num.format(otherFee,"0.0"));
		all.setAllFee(allFee==null ? "0" : Num.format(allFee,"0.0"));
		all.setEndTime("总合计");
		
		return all;
	}


	/**
	 * 构建查询条件
	 */
	private Predicate buildPredicate(Root<OutBill> root, CriteriaBuilder cb, OutBillVo vo)
	{
		String beginTime_start = vo.getBeginTime_start(); // 开始时间
		String beginTime_end = vo.getBeginTime_end(); // 结束时间
		String carId = vo.getCarId(); // 车辆ID
		String driver = vo.getDriver(); // 司机
		String organId = vo.getOrganId(); // 用车部门ID
		String user = vo.getUser(); // 用车人
		String carStatus = vo.getCarStatus(); // 车辆状况
		String dataSource = vo.getDataSource(); //数据来源（申请、登记）
		String queryForm = vo.getQueryForm(); //请求来源

		List<Predicate> predicates = new ArrayList<Predicate>();
		Date d = null;

		// 开始时间
		if(!xx.isEmpty(beginTime_start))
		{
			d = xx.toDate(beginTime_start);
			predicates.add(cb.greaterThanOrEqualTo(root.<Date> get("beginTime"), d));
		}

		// 结束时间
		if(!xx.isEmpty(beginTime_end))
		{
			d = xx.toTime(beginTime_end + " 23:59:59");
			predicates.add(cb.lessThanOrEqualTo(root.<Date> get("beginTime"), d));
		}

		// 车辆ID
		if(!xx.isEmpty(carId))
		{
			long a = xx.toLong(carId);
			predicates.add(cb.equal(root.get("car").get("id"), a));
		}

		// 司机
		if(!xx.isEmpty(driver))
		{
			predicates.add(cb.like(root.<String> get("driver"), "%" + driver + "%"));
		}

		// 用车部门ID
		if(!xx.isEmpty(organId))
		{
			long a = xx.toLong(organId);
			predicates.add(cb.equal(root.get("organ").get("id"), a));
		}

		// 用车人
		if(!xx.isEmpty(user))
		{
			predicates.add(cb.like(root.<String> get("user"), "%" + user + "%"));
		}

		// 车辆状况
		if(!xx.isEmpty(carStatus))
		{
			predicates.add(cb.like(root.<String> get("carStatus"), "%" + carStatus + "%"));
		}
		
		//数据来源
		if(!xx.isEmpty(dataSource))
		{
			predicates.add(cb.equal(root.get("dataSource"), dataSource));
		}

		//状态
		if("back".equals(queryForm))
		{//回录查询：待回录、完成
			predicates.add(cb.or(cb.equal(root.get("state").get("code"), "07")
					,cb.equal(root.get("state").get("code"), "08")));
		}
		else
		{//登记查询、单据统计查询：完成
			predicates.add(cb.equal(root.get("state").get("code"), "08"));
		}
		

		return cb.and(predicates.toArray(new Predicate[predicates.size()]));
	}

	/**
	 * 获取费率
	 */
	public Rate getRate()
	{
		Rate rate = new Rate();
		List<CommonProperty> ls = commonPropertyDao.findBySystem("car");
		for (CommonProperty p : ls)
		{
			if("01".equals(p.getCode()))
			{// 起步价
				rate.setStartingPrice(xx.toDouble(p.getValue()));
			}
			else if("02".equals(p.getCode()))
			{// 每公里单价
				rate.setUnitPrice(xx.toDouble(p.getValue()));
			}
			else if("03".equals(p.getCode()))
			{// 每小时单价
				rate.setTimePrice(xx.toDouble(p.getValue()));
			}
		}
		return rate;
	}
	
	/**
	 * 导出Excel查询
	 */
	public List<OutBill> exportExcelQuery(final OutBillVo vo)
	{
		Sort sort = new Sort(Direction.DESC, "addTime"); // 排序
		//动态查询对象
		Specification<OutBill> specification = new Specification<OutBill>()
		{
			@Override
			public Predicate toPredicate(Root<OutBill> root, CriteriaQuery<?> cq, CriteriaBuilder cb)
			{
				return buildPredicate(root, cb, vo);
			}
		};

		// 查询
		List<OutBill> list = dao.findAll(specification, sort);
		return list;
	}
	
	/**
	 * 用车申请查询
	 */
	public Result queryApply(final OutBillVo vo,final Long userId)
	{
		Sort sort = new Sort("desc".equals(vo.getOrder())?Direction.DESC:Direction.ASC, vo.getSort()); // 排序
		Pageable pageable = new PageRequest(vo.getPage() - 1, vo.getRows(), sort); // 分页查询条件

		//动态查询对象
		Specification<OutBill> specification = new Specification<OutBill>()
		{
			@Override
			public Predicate toPredicate(Root<OutBill> root, CriteriaQuery<?> cq, CriteriaBuilder cb)
			{
				String beginTime_start = vo.getBeginTime_start(); // 开始时间
				String beginTime_end = vo.getBeginTime_end(); // 结束时间
				String user = vo.getUser(); // 用车人

				List<Predicate> predicates = new ArrayList<Predicate>();
				Date d = null;

				// 开始时间
				if(!xx.isEmpty(beginTime_start))
				{
					d = xx.toDate(beginTime_start);
					predicates.add(cb.greaterThanOrEqualTo(root.<Date> get("beginTime"), d));
				}

				// 结束时间
				if(!xx.isEmpty(beginTime_end))
				{
					d = xx.toTime(beginTime_end + " 23:59:59");
					predicates.add(cb.lessThanOrEqualTo(root.<Date> get("beginTime"), d));
				}

				// 用车人
				if(!xx.isEmpty(user))
				{
					predicates.add(cb.like(root.<String> get("user"), "%" + user + "%"));
				}
				
				predicates.add(cb.equal(root.get("applyUserId"), userId)); //用户ID

				return cb.and(predicates.toArray(new Predicate[predicates.size()]));
			}
		};

		// 查询
		Page<OutBill> page = dao.findAll(specification, pageable);
		Result result = Result.getResult(page);

		//构建合计项
		List<OutBillFooter> footer = new ArrayList<>();
		//(1)标题
		OutBillFooter title = new OutBillFooter();
		title.setUseNum("使用里程");
		title.setUseTime("总时长");
		title.setWaitTime("等待时长");
		title.setFuelNum("加油金额");
		title.setOtherFee("过 / 停费");
		title.setAllFee("总费用");
		footer.add(title);

		//(2)本页合计
		List<OutBill> ls = page.getContent();
		footer.add(this.getOnePageSum(ls));
		
		//(3)所有符合条件的合计项
		//footer.add(this.getAllSum(vo));
		
		result.setFooter(footer);
		return result;
	}
	
	/**
	 * 用车审核查询
	 */
	public Result queryAudit(final OutBillVo vo,final String organCode)
	{
		Sort sort = new Sort("desc".equals(vo.getOrder())?Direction.DESC:Direction.ASC, vo.getSort()); // 排序
		Pageable pageable = new PageRequest(vo.getPage() - 1, vo.getRows(), sort); // 分页查询条件

		//动态查询对象
		Specification<OutBill> specification = new Specification<OutBill>()
		{
			@Override
			public Predicate toPredicate(Root<OutBill> root, CriteriaQuery<?> cq, CriteriaBuilder cb)
			{

				List<Predicate> predicates = new ArrayList<Predicate>();

				predicates.add(cb.equal(root.get("organ").get("str3"), organCode)); //机构编码
				predicates.add(cb.equal(root.get("state").get("code"), "02")); //待审核

				return cb.and(predicates.toArray(new Predicate[predicates.size()]));
			}
		};

		// 查询
		Page<OutBill> page = dao.findAll(specification, pageable);
		Result result = Result.getResult(page);

		return result;
	}

	/**
	 * 部门单据 查询
	 */
	public Result queryOrgan(final OutBillVo vo,final String organCode)
	{
		Sort sort = new Sort("desc".equals(vo.getOrder())?Direction.DESC:Direction.ASC, vo.getSort()); // 排序
		Pageable pageable = new PageRequest(vo.getPage() - 1, vo.getRows(), sort); // 分页查询条件

		//动态查询对象
		Specification<OutBill> specification = new Specification<OutBill>()
		{
			@Override
			public Predicate toPredicate(Root<OutBill> root, CriteriaQuery<?> cq, CriteriaBuilder cb)
			{
				return buildPredicate4Organ(root, cb,vo, organCode);
			}
			
		};

		// 查询
		Page<OutBill> page = dao.findAll(specification, pageable);
		Result result = Result.getResult(page);

		//构建合计项
		List<OutBillFooter> footer = new ArrayList<>();
		//(1)标题
		OutBillFooter title = new OutBillFooter();
		title.setUseNum("使用里程");
		title.setUseTime("总时长");
		title.setWaitTime("等待时长");
		title.setFuelNum("加油金额");
		title.setOtherFee("过 / 停费");
		title.setAllFee("总费用");
		footer.add(title);

		//(2)本页合计
		List<OutBill> ls = page.getContent();
		footer.add(this.getOnePageSum(ls));
		
		//(3)所有符合条件的合计项
		footer.add(this.getAllSum4Organ(vo,organCode));
		
		result.setFooter(footer);
		return result;
	}
	
	/**
	 * 数据合计：部门单据是查询
	 */
	private OutBillFooter getAllSum4Organ(OutBillVo vo,String organCode)
	{

		CriteriaBuilder cb = em.getCriteriaBuilder();
		CriteriaQuery<Tuple> cq = cb.createTupleQuery();

		// from
		Root<OutBill> root = cq.from(OutBill.class);

		// where
		cq.where(this.buildPredicate4Organ(root, cb,vo,organCode));

		return this.getSumData(cb, cq, root);
	}
	
	/**
	 * 构造条件：部门单据查询 
	 */
	private Predicate buildPredicate4Organ(Root<OutBill> root, CriteriaBuilder cb,final OutBillVo vo, final String organCode)
	{
		String beginTime_start = vo.getBeginTime_start(); // 开始时间
		String beginTime_end = vo.getBeginTime_end(); // 结束时间
		String user = vo.getUser(); // 用车人
		String applyUserName = vo.getApplyUserName(); //申请用户姓名

		List<Predicate> predicates = new ArrayList<Predicate>();
		Date d = null;

		// 开始时间
		if(!xx.isEmpty(beginTime_start))
		{
			d = xx.toDate(beginTime_start);
			predicates.add(cb.greaterThanOrEqualTo(root.<Date> get("beginTime"), d));
		}

		// 结束时间
		if(!xx.isEmpty(beginTime_end))
		{
			d = xx.toTime(beginTime_end + " 23:59:59");
			predicates.add(cb.lessThanOrEqualTo(root.<Date> get("beginTime"), d));
		}

		// 用车人
		if(!xx.isEmpty(user))
		{
			predicates.add(cb.like(root.<String> get("user"), "%" + user + "%"));
		}

		// 用车人
		if(!xx.isEmpty(applyUserName))
		{
			predicates.add(cb.like(root.<String> get("applyUserName"), "%" + applyUserName + "%"));
		}
		
		//部门编码
		predicates.add(cb.equal(root.get("organ").get("str3"), organCode)); //机构编码
		
		//状态：待回录、完成
		predicates.add(cb.or(cb.equal(root.get("state").get("code"), "07")
				,cb.equal(root.get("state").get("code"), "08")));

		return cb.and(predicates.toArray(new Predicate[predicates.size()]));
	}
	
	/**
	 * 出车回录 查询
	 */
	public Result queryBack(final OutBillVo vo)
	{
		Sort sort = new Sort("desc".equals(vo.getOrder())?Direction.DESC:Direction.ASC, vo.getSort()); // 排序
		Pageable pageable = new PageRequest(vo.getPage() - 1, vo.getRows(), sort); // 分页查询条件

		//动态查询对象
		Specification<OutBill> specification = new Specification<OutBill>()
		{
			@Override
			public Predicate toPredicate(Root<OutBill> root, CriteriaQuery<?> cq, CriteriaBuilder cb)
			{
				return buildPredicate(root, cb, vo);
			}
			
		};

		// 查询
		Page<OutBill> page = dao.findAll(specification, pageable);
		Result result = Result.getResult(page);

		//构建合计项
		List<OutBillFooter> footer = new ArrayList<>();
		//(1)标题
		OutBillFooter title = new OutBillFooter();
		title.setUseNum("使用里程");
		title.setUseTime("总时长");
		title.setWaitTime("等待时长");
		title.setFuelNum("加油金额");
		title.setOtherFee("过 / 停费");
		title.setAllFee("总费用");
		footer.add(title);

		//(2)本页合计
		List<OutBill> ls = page.getContent();
		footer.add(this.getOnePageSum(ls));
		
		//(3)所有符合条件的合计项
		footer.add(this.getAllSum(vo));
		
		result.setFooter(footer);
		return result;
	}
	
	
}
